"""
DIP智能报表系统 - 基于自然语言的数据库查询
支持用户使用自然语言描述查询需求，AI自动生成并执行SQL语句
"""

import os
import sys
import logging
from datetime import datetime
from dotenv import load_dotenv
import pandas as pd
from mysql.connector import Error
import mysql.connector
from langchain_openai import ChatOpenAI
from flask import Flask, request, jsonify
import json

# 加载环境变量
load_dotenv()

# Flask app 将在注册路由时传入
app = None
print("【智能报表】等待 Flask 应用注册")

# 获取logger实例（使用app.py中已配置的日志系统，不重复配置）
logger = logging.getLogger(__name__)

# 不再在这里创建 Flask 应用，而是使用上面导入的 app

# 全局DIPReport实例
report_instance = None


def get_report_instance():
    """获取或创建DIPReport实例"""
    global report_instance
    if report_instance is None:
        report_instance = DIPReport()
    return report_instance


class DIPReport:
    """DIP智能报表类 - AI驱动的自然语言查询系统"""

    def __init__(self):
        """初始化报表系统"""
        # 数据库配置
        self.db_config = {
            'host': os.getenv('DB_HOST', 'localhost'),
            'port': int(os.getenv('DB_PORT', 3306)),
            'user': os.getenv('DB_USER', 'root'),
            'password': os.getenv('DB_PASSWORD', ''),
            'database': os.getenv('DB_NAME', 'health_boot')
        }

        self.connection = None
        
        # 字段中文映射
        self.field_name_mapping = {
            'id': '主键ID',
            'patient_id': '患者ID',
            'patient_name': '患者姓名',
            'hospitalization_no': '住院号',
            'sex': '性别',
            'age': '年龄',
            'identity_card': '身份证',
            'series_name': '入组名称',
            'dip_code': 'DIP诊断编码',
            'dip_series_name': 'DIP分组病种名称',
            'dip_series_operation_name': 'DIP手术分组名称',
            'surgical_code': '手术编码',
            'surgical_type': '手术类型',
            'score_valut': 'DIP分值',
            'total_cost': '总费用',
            'average_cost': '平均费用',
            'lower_limit': '费用下限',
            'upper_limit': '费用上限',
            'full_amount': '自费金额',
            'registration_fee': '挂号费',
            'card_fee': '诊疗卡费',
            'material_fee': '医用材料费',
            'western_medicine': '西药费',
            'chinese_medicine_prep': '中成药费',
            'chinese_herbal': '中草药费',
            'lab_test': '化验费',
            'imaging': '影像检查费',
            'therapy': '治疗费',
            'surgery': '手术费',
            'anesthesia': '麻醉费',
            'nursing': '护理费',
            'diet': '膳食费',
            'bed_fee': '床位费',
            'blood_transfusion': '输血费',
            'oxygen': '氧气费',
            'consumables': '一次性医用耗材费',
            'zy_therapy': '中医治疗费',
            'dep_id': '部门ID',
            'dep_name': '科室名称',
            'resident_physician': '住院医师',
            'medical_insurance_type': '医保类型',
            'insured_institutions': '参保机构',
            'admission_date': '入院时间',
            'out_date': '出院时间',
            'settlement_date': '结算日期',
            'create_time': '创建时间',
            'update_time': '更新时间',
            'status': '状态',
            'deleted': '删除标记',
            'tenant_id': '租户编号',
            'creator': '创建者',
            'updater': '更新者',
            'index_id': '主页ID'
        }

        # 初始化AI模型用于SQL生成
        self.llm = ChatOpenAI(
            model=os.getenv('MODEL_NAME', 'deepseek-chat'),
            openai_api_key=os.getenv('OPENAI_API_KEY'),
            openai_api_base=os.getenv('OPENAI_BASE_URL'),
            temperature=0.1  # 低温度以获得更准确的SQL
        )

        # SQL生成提示词模板
        self.sql_prompt_template = """你是一个专业的SQL查询生成专家。根据用户的自然语言需求，生成准确的MySQL查询语句。

数据库表：hospital_dip_series（DIP分组结果表）

表结构和字段说明：
【基本信息】
- id: 主键ID（bigint）
- patient_id: 患者ID
- patient_name: 患者姓名
- hospitalization_no: 住院号
- sex: 性别
- age: 年龄
- identity_card: 身份证

【DIP相关】
- series_name: 入组名称（DIP病种名称）
- dip_code: DIP诊断编码
- dip_series_name: DIP分组病种名称
- dip_series_operation_name: DIP手术分组名称
- surgical_code: 手术编码
- surgical_type: 手术类型（1=保守治疗, 2=手术治疗）
- score_valut: DIP分值（decimal）

【费用信息】
- total_cost: 总费用（decimal）
- average_cost: 平均费用（decimal）
- lower_limit: 费用下限（decimal）
- upper_limit: 费用上限（decimal）
- full_amount: 自费金额（decimal）

【各项费用明细】
- registration_fee: 挂号费
- card_fee: 诊疗卡费
- material_fee: 医用材料费
- western_medicine: 西药费
- chinese_medicine_prep: 中成药费
- chinese_herbal: 中草药费
- lab_test: 化验费
- imaging: 影像检查费
- therapy: 治疗费
- surgery: 手术费
- anesthesia: 麻醉费
- nursing: 护理费
- diet: 膳食费
- bed_fee: 床位费
- blood_transfusion: 输血费
- oxygen: 氧气费
- consumables: 一次性医用耗材费
- zy_therapy: 中医治疗费

【医疗机构信息】
- dep_id: 部门ID
- dep_name: 科室名称
- resident_physician: 住院医师
- medical_insurance_type: 医保类型（1=职工, 2=居民）
- insured_institutions: 参保机构

【时间信息】
- admission_date: 入院时间（datetime）
- out_date: 出院时间（datetime）
- settlement_date: 结算日期（datetime）
- create_time: 创建时间（datetime）
- update_time: 更新时间（datetime）

【系统字段】
- status: 状态（tinyint，默认0）
- deleted: 删除标记（bit，0=未删除，1=已删除）
- tenant_id: 租户编号
- creator: 创建者
- updater: 更新者
- index_id: 主页ID

重要规则：
1. **必须**在WHERE子句中添加 `deleted = 0` 或 `CAST(deleted AS UNSIGNED) = 0` 来过滤已删除的记录（deleted是bit(1)类型）
2. datetime字段格式为'YYYY-MM-DD HH:MM:SS'，可以用DATE()函数转换日期部分
3. 使用LIMIT限制返回结果数量（默认100条，除非用户明确要求更多）
4. 对于统计查询，使用GROUP BY和聚合函数（COUNT, SUM, AVG, MAX, MIN等）
5. 只生成SELECT查询，不要生成INSERT/UPDATE/DELETE
6. SQL语句必须是有效的MySQL 8.0语法
7. 只返回SQL语句，不要有任何解释、注释或其他文字
8. surgical_type字段：1=保守治疗，2=手术治疗
9. medical_insurance_type字段：1=职工医保，2=居民医保
10. 涉及住院天数时，使用 DATEDIFF(out_date, admission_date) 计算
11. 如果用户没有指定时间范围，不要自动添加时间限制条件
12. 数值字段（费用、分值等）都是decimal类型，可以直接比较
13. 确保所有WHERE条件都合理，避免过度限制导致无结果
14. **建议明确指定字段列表**（10-20个关键字段），避免使用SELECT *返回过多字段
15. 优先选择用户关心的核心字段：患者信息、DIP相关、费用信息等
16. 如果用户明确要求"所有字段"、"完整信息"或"SELECT *"，则使用SELECT *
17. 对于"查询XX信息"类需求，选择相关的10-15个关键字段即可,系统字段不需要返回,主键不返回

用户需求：{user_query}

请生成SQL查询语句："""

    def get_connection(self):
        """获取数据库连接"""
        try:
            # 先关闭旧连接（如果存在且已断开）
            if self.connection is not None:
                try:
                    if not self.connection.is_connected():
                        logger.info("检测到旧连接已断开，关闭旧连接")
                        self.connection.close()
                        self.connection = None
                except:
                    logger.warning("检查旧连接状态时出错，强制关闭")
                    try:
                        self.connection.close()
                    except:
                        pass
                    self.connection = None
            
            # 如果没有连接或连接已断开，创建新连接
            if self.connection is None:
                logger.info(f"正在连接数据库: {self.db_config['host']}:{self.db_config['port']}/{self.db_config['database']}")
                logger.info(f"使用用户: {self.db_config['user']}")
                print(f"【数据库】正在连接...", flush=True)
                
                # 添加多个超时设置，防止卡死
                connection_config = self.db_config.copy()
                connection_config['connection_timeout'] = 3  # 连接超时3秒
                connection_config['autocommit'] = True  # 自动提交
                connection_config['use_pure'] = True  # 使用纯Python实现，避免C扩展问题
                connection_config['consume_results'] = True  # 自动消费未读结果
                
                logger.info("开始建立连接...")
                self.connection = mysql.connector.connect(**connection_config)
                logger.info("数据库连接成功")
                print("【数据库】连接成功 ✓", flush=True)
            
            return self.connection
            
        except mysql.connector.Error as e:
            logger.error(f"数据库连接失败 (MySQL Error): {e}")
            print(f"【数据库】连接失败: {e}", flush=True)
            self.connection = None
            return None
        except Exception as e:
            logger.error(f"数据库连接异常 (Exception): {type(e).__name__}: {e}")
            print(f"【数据库】连接异常: {e}", flush=True)
            self.connection = None
            return None

    def close_connection(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            logger.info("数据库连接已关闭")

    def translate_field_names(self, data_dict):
        """将英文字段名转换为中文字段名"""
        translated = {}
        for eng_key, value in data_dict.items():
            # 使用中文映射，如果没有映射则保持原名
            chinese_key = self.field_name_mapping.get(eng_key, eng_key)
            translated[chinese_key] = value
        return translated

    def _get_table_schema(self):
        """获取表结构信息（用于AI参考）"""
        return """
        hospital_dip_series表完整字段列表：
        
        【基本信息】id, patient_id, patient_name, hospitalization_no, sex, age, identity_card
        
        【DIP相关】series_name, dip_code, dip_series_name, dip_series_operation_name, 
                  surgical_code, surgical_type(1=保守/2=手术), score_valut
        
        【费用汇总】total_cost, average_cost, lower_limit, upper_limit, full_amount
        
        【费用明细】registration_fee(挂号), card_fee(诊疗卡), material_fee(医用材料), 
                   western_medicine(西药), chinese_medicine_prep(中成药), chinese_herbal(中草药),
                   lab_test(化验), imaging(影像检查), therapy(治疗), surgery(手术), 
                   anesthesia(麻醉), nursing(护理), diet(膳食), bed_fee(床位),
                   blood_transfusion(输血), oxygen(氧气), consumables(耗材), zy_therapy(中医治疗)
        
        【医疗信息】dep_id, dep_name, resident_physician, 
                   medical_insurance_type(1=职工/2=居民), insured_institutions
        
        【时间信息】admission_date, out_date, settlement_date, create_time, update_time
        
        【系统字段】status, deleted(0=未删除), tenant_id, creator, updater, index_id
        """

    def generate_sql_from_natural_language(self, user_query):
        """使用AI将自然语言转换为SQL查询"""
        try:
            # 构造提示词
            prompt = self.sql_prompt_template.format(user_query=user_query)

            # 调用AI生成SQL
            logger.info(f"正在为查询生成SQL: {user_query}")
            response = self.llm.invoke(prompt)

            # 提取SQL语句
            sql = response.content.strip()

            # 清理SQL语句（移除可能的markdown标记）
            if sql.startswith("```sql"):
                sql = sql[6:]
            if sql.startswith("```"):
                sql = sql[3:]
            if sql.endswith("```"):
                sql = sql[:-3]
            sql = sql.strip()

            # 注意：不再强制替换SELECT *，让AI自由生成字段
            # 但会在提示词中引导AI避免使用SELECT *
            if 'SELECT *' in sql.upper() or 'SELECT  *' in sql.upper():
                logger.warning("AI生成了 SELECT * 查询，可能返回大量字段")
                print("【提示】查询将返回所有字段（140+个），建议具体指定需要的字段", flush=True)

            logger.info(f"生成的SQL: {sql}")
            # 使用 try-except 保护 print，避免编码错误 - 添加flush=True强制输出
            try:

                print(sql, flush=True)
                print(f"{'='*80}\n", flush=True)
            except UnicodeEncodeError:
                logger.warning("控制台输出SQL时发生编码错误，跳过输出")

            return sql

        except Exception as e:
            logger.error(f"生成SQL时发生错误: {e}")
            return None

    def execute_natural_language_query(self, user_query):
        """执行自然语言查询"""
        try:
            # 1. 生成SQL
            sql = self.generate_sql_from_natural_language(user_query)
            if not sql:
                print("[错误] SQL生成失败")
                return None

            # 2. 执行SQL查询
            connection = self.get_connection()
            if not connection:
                print("[错误] 数据库连接失败", flush=True)
                return None

            logger.info("正在执行SQL查询...")
            print("[执行中] 正在执行查询...", flush=True)
            
            # 使用cursor方式执行，避免pandas兼容性问题
            cursor = connection.cursor(dictionary=True)
            logger.info("Cursor创建成功，开始执行SQL")
            cursor.execute(sql)
            logger.info("SQL执行成功，开始获取结果")
            results = cursor.fetchall()
            logger.info(f"成功获取 {len(results)} 条记录")
            cursor.close()
            logger.info("Cursor已关闭")
            
            # 检查结果数量,避免处理过多数据导致崩溃
            if len(results) > 1000:
                logger.warning(f"查询结果过多 ({len(results)} 条),仅处理前1000条")
                print(f"\n查询返回 {len(results)} 条记录,仅显示前1000条", flush=True)
                results = results[:1000]
            
            # 转换为DataFrame - 完全避免访问DataFrame属性
            logger.info("开始转换为DataFrame...")
            df = pd.DataFrame(results)
            # 不访问df.shape，直接用results长度
            result_count = len(results)
            logger.info(f"数据已转换为DataFrame, 记录数: {result_count}")

            # 3. 显示结果 - 终极极简模式,只用Python基本操作 - 添加flush=True
            print(f"\n{'='*80}", flush=True)
            print(f"[结果] 查询结果", flush=True)
            print(f"{'='*80}\n", flush=True)

            if result_count > 0:
                # 终极方案:完全不访问DataFrame,只用results原始数据
                print(f"[成功] 查询成功", flush=True)
                print(f"[统计] 记录数: {result_count}", flush=True)
                
                # 从results[0]获取字段名,完全不碰df
                try:
                    if results and len(results) > 0:
                        cols = list(results[0].keys())
                        field_count = len(cols)
                        print(f"[字段] 字段数: {field_count}", flush=True)
                        
                        if field_count <= 10:
                            print(f"[字段列表] 字段: {', '.join(cols)}", flush=True)
                        else:
                            print(f"[字段列表] 前10个字段: {', '.join(cols[:10])} ...", flush=True)
                            print(f"   (共{field_count}个字段)", flush=True)
                except Exception as e:
                    logger.error(f"显示字段信息时出错: {e}")
                    print(f"[字段] 查询包含多个字段", flush=True)

                print(f"\n[提示] 如需查看完整数据:", flush=True)
                print(f"   1. 使用 HTTP API: POST http://localhost:5000/api/query", flush=True)
                print(f"   2. 在查询中添加'导出'关键词,自动导出到 Excel", flush=True)
            else:
                print("[警告] 未查询到数据", flush=True)
                print("[提示] 提示：请检查查询条件是否过于严格，或尝试修改查询描述", flush=True)

            print(f"\n{'='*80}\n", flush=True)


            return df

        except Exception as e:
            logger.error(f"处理查询时发生错误: {e}")

            return None




# ==================== HTTP API 接口 ====================

def api_query():
    """
    HTTP API接口 - 自然语言查询
    """
    try:
        # 记录原始请求信息
        logger.info(f"收到请求 - Content-Type: {request.content_type}, 数据长度: {request.content_length}")
        
        # 支持三种请求格式：
        # 1. JSON格式: {"query": "..."}
        # 2. Java纯文本: 直接发送查询文本
        # 3. 其他JSON格式
        
        data = None
        parse_error = None
        
        # 检查是否是纯文本请求（Java HttpUtils.sendPostText）
        if request.content_type and 'text/plain' in request.content_type:
            # Java纯文本请求，直接读取文本作为query
            raw_data = request.get_data(as_text=True).strip()
            logger.info(f"检测到纯文本请求: {raw_data[:100]}")
            data = {"query": raw_data}
        else:
            # 尝试JSON解析
            try:
                data = request.get_json(force=True)  # force=True 忽略Content-Type
                logger.info(f"成功解析JSON: {data}")
            except Exception as e:
                parse_error = str(e)
                logger.warning(f"JSON解析失败: {e}")
                
                # 如果JSON解析失败，尝试获取原始文本
                raw_data = request.get_data(as_text=True).strip()
                logger.info(f"原始数据: {raw_data[:200]}")  # 只记录前200字符
                
                if raw_data:
                    try:
                        data = json.loads(raw_data)
                        logger.info(f"通过原始数据解析成功: {data}")
                    except Exception as e2:
                        logger.warning(f"原始数据JSON解析也失败: {e2}")
                        # 如果是纯文本，当作query参数
                        data = {"query": raw_data}
                        logger.info(f"作为纯文本处理: {data}")
        
        if not data or 'query' not in data:
            error_msg = f"缺少必需参数: query. 解析错误: {parse_error}, 收到数据: {data}"
            logger.error(error_msg)
            return jsonify({
                "success": False,
                "message": "缺少必需参数: query",
                "error": "参数错误",
                "debug_info": error_msg if parse_error else None
            }), 400
        
        user_query = data.get('query', '').strip()

        if not user_query:
            return jsonify({
                "success": False,
                "message": "查询内容不能为空",
                "error": "参数错误"
            }), 400
        
        logger.info(f"收到HTTP查询请求: {user_query}")
        
        # 获取报表实例
        report = get_report_instance()
        
        # 生成SQL
        sql = report.generate_sql_from_natural_language(user_query)
        if not sql:
            return jsonify({
                "success": False,
                "message": "SQL生成失败",
                "error": "AI生成SQL失败"
            }), 500
        
        # 执行查询
        connection = report.get_connection()
        if not connection:
            return jsonify({
                "success": False,
                "message": "数据库连接失败",
                "error": "数据库连接错误"
            }), 500
        
        cursor = connection.cursor(dictionary=True)
        try:
            logger.info(f"开始执行SQL: {sql[:200]}...")
        except:
            logger.info("开始执行SQL...")
        cursor.execute(sql)
        logger.info("SQL执行成功,正在获取结果...")
        results = cursor.fetchall()
        logger.info(f"成功获取 {len(results)} 条记录")
        cursor.close()
        
        # 检查结果数量,避免返回过多数据导致崩溃
        if len(results) > 1000:
            logger.warning(f"查询结果过多 ({len(results)} 条),仅返回前1000条")
            results = results[:1000]
        
        # 转换为DataFrame
        logger.info("开始转换为DataFrame...")
        df = pd.DataFrame(results)
        logger.info(f"DataFrame创建成功,形状: {df.shape}")
        
        # 检查字段数量
        field_count = len(df.columns)
        if field_count > 50:
            logger.warning(f"字段数过多({field_count}个),数据量较大,请耐心等待...")
            print(f"【提示】查询包含{field_count}个字段,正在处理数据...", flush=True)
        
        # 处理DataFrame中的特殊类型（datetime、Decimal等）
        # 使用更高效的方法：直接转换整个DataFrame
        def convert_value(val):
            try:
                if pd.isna(val):
                    return None
                elif isinstance(val, datetime):
                    return val.strftime('%Y-%m-%d %H:%M:%S')
                elif hasattr(val, '__float__'):  # Decimal等数值类型
                    return float(val)
                elif hasattr(val, 'item'):  # numpy类型
                    return val.item()
                else:
                    return str(val) if val is not None else None
            except Exception as e:
                logger.warning(f"转换值时出错: {e}, 值类型: {type(val)}")
                return str(val) if val is not None else None
        
        # 转换为字典列表 - 优化版本：使用to_dict()方法更快
        logger.info("开始转换数据格式...")
        data_list = []
        try:
            # 使用records方向转换，比iterrows快很多
            records = df.to_dict('records')
            logger.info(f"成功转换为字典列表，共{len(records)}条记录")
            
            for idx, row_dict in enumerate(records):
                try:
                    # 转换特殊类型
                    converted = {k: convert_value(v) for k, v in row_dict.items()}
                    # 转换为中文字段名
                    row_dict_cn = report.translate_field_names(converted)
                    data_list.append(row_dict_cn)
                    
                    # 每处理100条记录打印一次进度（仅当数据量大时）
                    if len(records) > 100 and (idx + 1) % 100 == 0:
                        logger.info(f"已处理 {idx + 1}/{len(records)} 条记录")
                        
                except Exception as e:
                    logger.error(f"转换第 {idx} 行数据时出错: {e}")
                    continue
                    
            logger.info(f"数据转换完成，成功转换{len(data_list)}条记录")
            
        except Exception as e:
            logger.error(f"遍历数据时出错: {e}")
            return jsonify({
                "success": False,
                "message": "数据转换失败",
                "error": str(e)
            }), 500
        

        
        # 返回结果
        response = {
            "success": True,
            "data": data_list,
            "count": len(data_list),
            "sql": sql,
            "message": f"查询成功，共 {len(data_list)} 条记录"
        }
        

        logger.info(f"HTTP查询成功，返回 {len(data_list)} 条记录")
        return jsonify(response), 200
        
    except Exception as e:
        logger.error(f"HTTP查询发生错误: {str(e)}")
        return jsonify({
            "success": False,
            "message": "查询执行失败",
            "error": str(e)
        }), 500






# 注意：/api/dip/analyze_medical_text 路由已在 dip_series_bl_entrance.py 中定义
# 这里不再重复定义，避免路由冲突


def start_http_server(host='0.0.0.0', port=5000, debug=False):
    """启动HTTP服务器"""
    try:
        print("\n" + "="*60)
        print("[启动] DIP综合服务")
        print("="*60)
        print(f"\n[地址] 服务地址: http://{host}:{port}")
        print(f"\n[API] API接口：")
        print(f"  - POST /api/query                    - 智能报表查询")
        print(f"  - POST /api/dip/analyze_medical_text - 病历解析")
        print(f"  - GET  /api/health                   - 健康检查")
        print(f"  - GET  /api/examples                 - 查询示例")
        print("\n[Java] Java客户端调用：")
        print(f'  String response = HttpUtils.sendPostText("http://localhost:{port}/api/query", "查询...");\n')
        
        app.run(host=host, port=port, debug=debug)
    except Exception as e:
        logger.error(f"服务启动失败: {e}")
        print(f"\n[错误] 服务启动失败: {e}")
        raise


def register_routes(flask_app):
    """注册路由到Flask应用"""
    global app
    app = flask_app
    
    # 手动注册路由（不使用装饰器）
    app.add_url_rule('/api/query', 'api_query', api_query, methods=['POST'])

    print("【智能报表】路由已注册到 Flask 应用")


if __name__ == "__main__":
    import sys
    
    # 检查命令行参数
    if len(sys.argv) > 1 and sys.argv[1] == 'http':
        # HTTP服务模式
        port = int(sys.argv[2]) if len(sys.argv) > 2 else 5000
        start_http_server(port=port, debug=True)

